from DataAccess.DBConnFactory import *

from string import Template

def query_sector_ticker(sector_class):	
	
	sql_tpl = Template('''select Symbol from IPROFILE where Iprofile7='${CLASS}' ''')
	sql_text = sql_tpl.substitute(CLASS = sector_class)
	
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	if r:
		return [each[0] for each in r]
	else:
		print '      err info...failed in querying sector ticker,', sector_class
		
def query_index_comp(ref_date, index):

	sql_tpl = Template('''select Symbol from IDCOMPT
						 where ICODE = '${TICK}'
						   and UPDATEDATE <= TO_DATE('${DATE}', 'yyyy-mm-dd')
						   and (CULLDATE > TO_DATE('${DATE}', 'yyyy-mm-dd')
								or CULLDATE is NULL
								or CULLDATE = TO_DATE('1900-01-01', 'yyyy-mm-dd')) ''')
	sql_text = sql_tpl.substitute(TICK = index, DATE = ref_date.isoformat())
	
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	if r:
		return [each[0] for each in r]
	else:
		print '      err info...failed in querying index component,', index